const SqliteUtil = appRequire('modules/SqliteUtil');
const PageResultVo = appRequire('vo/PageResultVo.js');
class CarnumDatasSqliteDao {
  constructor() {

  }
  static async create() {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    await db.run(`CREATE TABLE "carnum_datas" (
      "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
      "car_num" VARCHAR(255) DEFAULT '',
      "car_type" VARCHAR(255) DEFAULT '',
      "car_color" VARCHAR(255),
      "car_driver" VARCHAR(255),
      "car_dept" VARCHAR(255),
      "car_phone_num" VARCHAR(255),
      "car_address" VARCHAR(255),
      "car_regist_time" VARCHAR(255),
      "car_driver_num" VARCHAR(255),
      "car_remarks" VARCHAR(255),
      "is_hide" VARCHAR(10) DEFAULT '',
      "data_sort" integer
    );`);
    await db.close();
  }
  static async saveOne(user) {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    try {
      await db.run(`insert into sys_user
          (id,nick_name,username,password,phone_num,email,remarks,create_time,update_time,sort) 
          values(?,?,?,?,?,?,?,?,?,?)`,
        [user.id, user.nickName, user.username, user.password, user.phoneNum, user.email,user.remarks,user.createTime, user.updateTime,user.sort]);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async updateOne(user) {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    try {
      await db.run(`update sys_user 
        set nick_name=?,password=?,user_icon=?,phone_num=?,short_phone_num=?,email=?,sys_user_role_ids=?,remarks=?,update_time=?,is_used=?,is_hide=?,sort=? where id = ?`,
      [user.nickName, user.password, user.userIcon, user.phoneNum, user.shortPhoneNum, user.email, user.roleIds,user.remarks, user.updateTime, user.isUsed, user.isHide, user.sort,user.id]);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async deleteById(id) {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    try {
      await db.run(`delete from sys_user where id = ?`,
      [id]);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async deleteMany(ids) {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    try {
      console.log(ids);
      let sqlWhere = 'where id in (';
      for(let i = 0;i<ids.length;i++){
        if(i!=ids.length-1){
          sqlWhere+=`'${ids[i]}',`
        }else{
          sqlWhere+=`'${ids[i]}')`
        }
      }
      let sql = `delete from sys_user ${sqlWhere}`;
      console.log(sql);
      await db.run(sql);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  
  static async findOne(id) {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    try {
      let data = await db.get(`select id,username,nick_name as nickName,phone_num as phoneNum,sys_user_role_ids as roleIds,email,remarks from sys_user where id=?`,[id]);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findById(id) {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    try {
      let data = await db.get(`select * from sys_user where id=?`,[id]);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findAll() {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    try {
      let data = await SqliteUtil.each(db,`select * from sys_user`);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findByPage(page) {
    let db = await SqliteUtil.open(appConfig.dbPath2);
    try {
      let sqlWhere = '';
      let sqlParams = [(page.pageNum-1)*page.pageSize,page.pageSize];
      if(page.searchParams&&page.searchParams.username){
        sqlWhere +='where username=?'
        sqlParams.unshift(page.searchParams.username);
      }
      let result = await db.get(`select count(*) as totalNum from sys_user ${sqlWhere}`);
      let count = result.totalNum;
      let data = await SqliteUtil.each(db,`select id,username,nick_name as nickName,phone_num as phoneNum,sort from sys_user ${sqlWhere} order by create_time desc limit ?,?`,sqlParams);
      //加了await 会等待data拼装完成
      return PageResultVo.success('success',count,data);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }   
  }
}
module.exports = CarnumDatasSqliteDao;